package com.sms.user;

import com.sms.DB.*;
import com.sms.project.Task;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class User {
	// 检查登陆时的账号，密码以及身份是否正确，正确返回true，否则返回false
	public static boolean check(String role, String userno, String password) {
		String sql = "";
		if (role.equals("teacher")) {
			sql = "select * from teacherinfo where tno = '" + userno + "'";
		} else if(role.equals("student")) {
			sql = "select * from stuinfo where sno = '" + userno + "' " +
					" and isexist = '1'";
		} else {//身份为组长
			sql = "select * from stuinfo as si, groupinfo as gi " +
					"where si.sno = gi.leaderid " +
					"and si.sno = " + userno;
		}
		//System.out.println(sql);
		List<Map> list = OperateDB.executeQuery(sql);
		if (list.size() < 1) {// 数据库中没有该用户时，登陆失败，返回false
			return false;
		} else {
			Map map = list.get(0);
			String psw = (String) map.get("passkey");// 获得数据库中对应账号的密码
			//System.out.println(psw);
			if (!password.equals(psw)) {// 登陆时输入的密码与数据库中的不匹配,登陆失败，返回false
				return false;
			} else {// 登陆成功，返回true
				return true;
			}
		}

	}

	// 获得指定账号、身份的名字
	public static String getName(String role, String userno) {
		String sql = "";
		String name = "";

		if (role.equals("teacher")) {
			sql = "select * from teacherinfo where tno = '" + userno + "'";
		} else {
			sql = "select * from stuinfo where sno = '" + userno + "'";
		}

		List<Map> list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {
			Map map = list.get(0);
			if(role.equals("teacher")) {
				name = (String) map.get("tname");// 获得名字
			} else {
				name = (String)map.get("sname");
			}
		}
		return name;
	}
	
	//获得所有学生
	public static List<Student> getAllStudents() {
		String sql = "select * from stuinfo where isexist = '1'";
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}
	
	//获得指定类型的学生
	public static List<Student> getAllStudents(String stutype) {
		String append = "";
		if(stutype.equals("all")) {
			append = "";
		} else if(stutype.trim().equals("front")){
			append = " and stu_type = '前台'";
		} else if(stutype.trim().equals("back")){
			append = " and stu_type = '后台'";
		} else {
			append = " and stu_type = '美工'";
		}
		String sql = "select * from stuinfo where isexist = '1'" +
				append;
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}
	
	//获得指定项目的所有组员（包括组长）
	public static List<Student> getAllStudents(int proID) {
		String sql = "select si.* from groupinfo as gi, groupmember as gm, stuinfo as si " +
				" where si.sno = gm.memberid " +
				" and gi.groupid = gm.groupid " +
				" and gi.projectid = " + proID;
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}
	
	//获得指定项目的所有组员（不包括组长）
	public static List<Student> getMemberStudents(int proID) {
		String sql = "select si.* from groupinfo as gi, groupmember as gm, stuinfo as si " +
				" where si.sno = gm.memberid " +
				" and si.sno != gi.leaderid " +
				" and gi.groupid = gm.groupid " +
				" and gi.projectid = " + proID;
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}
	
	//获得指定项目的老师
	public static List<Teacher> getTeacher(int proID) {
		List<Teacher> tlist = null;
		List<Map> list = new ArrayList<Map>();
		String sql = "select te.* from taskinfo as ti, teacherinfo as te " +
				" where ti.fromID = te.tno " +
				" and ti.taskid = " + proID;

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {
			tlist = new ArrayList<Teacher>();
			Map map = list.get(0);
			Teacher t = new Teacher();
			t.init(map);
			tlist.add(t);
		}
		return tlist;
	}
	
	//获得所有的老师
	public static List<Teacher> getTeacher() {
		List<Teacher> tlist = null;
		List<Map> list = new ArrayList<Map>();
		String sql = "select * from teacherinfo";

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {
			tlist = new ArrayList<Teacher>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Teacher t = new Teacher();
				t.init(map);
				tlist.add(t);
			}
		}
		return tlist;
	}
	
	//获得指定项目的组长
	public static List<Student> getLeaderStudent(int proID) {
		String sql = "select si.* from groupinfo as gi, stuinfo as si " +
					" where si.sno = gi.leaderid " +
					" and gi.projectid = " + proID;
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}
	
	
	
	//?
	public static List<Student> getLeaderStudents(String userno) {
		String sql = "select si.* from taskinfo as ti, stuinfo as si " +
				" where fromid = '" + userno + "'" +
				" and ti.toid = si.sno";
		List<Student> slist = null;
		List<Map> list = null;
		
		//System.out.println(sql);
		list = OperateDB.executeQuery(sql);
		if(list.size() > 0) {//老师带有学生时才初始化slist
			slist = new ArrayList<Student>();
			for(int i=0; i<list.size(); i++) {
				Map map = list.get(i);
				Student s = new Student();
				s.init(map);
				slist.add(s);
			}
		}
		return slist;
	}

	// 获得老师的个人信息
	public static List<Teacher> getTeacherInfo(String userno) {
		List<Teacher> tlist = null;
		List<Map> list = new ArrayList<Map>();
		String sql = "select * from teacherinfo where tno = " + userno;

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有该教师的个人信息时才初始化tlist
			tlist = new ArrayList<Teacher>();
			Map map = list.get(0);
			Teacher t = new Teacher();
			t.init(map);
			tlist.add(t);
		}
		return tlist;
	}

	// 获得学生的个人信息 
	public static List<Student> getStudentInfo(String userno) {
		List<Student> slist = null;
		List<Map> list = new ArrayList<Map>();
		String sql = "select * from stuinfo where sno = " + userno;

		list = OperateDB.executeQuery(sql);
		if (list.size() > 0) {// 有该学生的个人信息时才初始化slist
			slist = new ArrayList<Student>();
			Map map = list.get(0);
			Student s = new Student();
			s.init(map);
			slist.add(s);
		}
		return slist;
	}
	
	//覃佳华 信息修改用的
	
	public static boolean modifyinfo( String userno,String name, String oripsw,String major,
		String type	,String newpsw){
		boolean a=false;
		List<Student> list=User.getStudentInfo(userno);
		Student student=list.get(0);
		String psw =student.getPassword();// 获得数据库中对应账号的密码		
		if(oripsw.equals(psw)){
			
			String sql="update stuinfo set sname='"+name+"',passkey='"+newpsw+"',stu_type='"+type+"',major='"+major+"' where sno='"+userno+"' ";
			if(OperateDB.update(sql)>0){
				a=true;			}
       }
		return a;
	}
	
	//老师改密码
	
	public static boolean modifyinfo( String userno,String name, String oripsw,
			String newpsw){
			boolean a=false;
			List<Teacher> list=User.getTeacherInfo(userno);
			Teacher teacher=list.get(0);
			String psw =teacher.getPassword();// 获得数据库中对应账号的密码		
			if(oripsw.equals(psw)){
				
				String sql="update teacherinfo set tname='"+name+"',passkey='"+newpsw+"' where tno='"+userno+"' ";
				if(OperateDB.update(sql)>0){
					a=true;			}
	       }
			return a;
		}
	
	// 老师 or 学生的密码修改,修改成功返回true，否则返回false
	public static boolean modifyPsw(String role, String userno, String origpsw,
			String newpsw) {
		String sql;
		if (role.equals("teacher")) {
			sql = "update teacherinfo set password = " + newpsw
					+ "where tno = " + userno;
		} else {
			sql = "update stuinfo set password = " + newpsw + "where sno = "
					+ userno;
		}

		int n = OperateDB.update(sql);
		if (n != 0) {
			return true;
		} else {
			return false;
		}
	}
	
	
	/*
	 * 删除指定编号的学员
	 */
	public static boolean delPeople(String[] delNo) {
		boolean isSuccess = true;
		if (delNo.length < 1) {
			isSuccess = false;
		} else {
			Connection conn = ConnectDB.getConnection();
			boolean autoCommit;
			try {
				autoCommit = conn.getAutoCommit();
				conn.setAutoCommit(false);// 取消自动提交

				for (int i = 0; i < delNo.length; i++) {
					String sno = delNo[i];
					
					//查看该学员是否还有参与项目
					String sql = "select count(*) as number from groupmember" + 
								" where memberid = '" + sno +  "' " ;
					List<Map> list = new ArrayList<Map>();
					int count = 0;
					
					//System.out.println(sql);
					list = OperateDB.executeQuery(sql);
					if(list.size() > 0) {
						Map map = list.get(0);
						count = Integer.parseInt(map.get("number").toString());
						
						if(count > 0) {
							isSuccess = false;
							break;
						} else {
							sql = "update stuinfo set isexist = '0' " +
									" where sno = '" + sno + "'";
							int n = OperateDB.update(sql);
							if (n == 0) {
								return false;
							} 
						}
					} else {
						isSuccess = false;
						break; // 跳出循环
					}
				}

				if (isSuccess) {
					conn.commit();// 手动提交
				} else {
					conn.rollback();
				}
				conn.setAutoCommit(autoCommit);// 恢复现场
			} catch (SQLException e) {
				e.printStackTrace();
				isSuccess = false;
			} catch (NumberFormatException e) {
				e.printStackTrace();
				isSuccess = false;
			} finally {
				OperateDB.close(conn);
			}
		}

		return isSuccess;
	}
	
	//添加人员为老师时
	public static boolean addTeacher(String tno, String tname, String sex) {
		boolean isSuccess = true;
		
		System.out.println(tno);
		System.out.println(tname);
		System.out.println(sex);
		String sql = "insert into teacherinfo values(?, ?, ?, '123456')";
		List<Object> tlist = new ArrayList<Object>();
		tlist.add(tno);
		tlist.add(tname);
		tlist.add(sex);
		OperateDB.setList(tlist);
		if(!OperateDB.insert(sql)) {
			isSuccess = false;
		} 
		return isSuccess;
	}
	
	//添加人员为学员时
	public static boolean addStudent(String sno, String sname, String sex, String type, String major, String tno) {
		boolean isSuccess = true;
		
		String sql = "insert into stuinfo values(?, ?, ?, '123456', ?, ?, ?, '1')";
		List<Object> slist = new ArrayList<Object>();
		slist.add(sno);
		slist.add(sname);
		slist.add(sex);
		String stu_type = "";
		if(type.equals("front")) {
			stu_type = "前台";
		} else if(type.equals("back")) {
			stu_type = "后台";
		} else if(type.equals("ps")) {
			stu_type = "美工";
		}
		slist.add(stu_type);
		slist.add(major);
		slist.add(tno);
		OperateDB.setList(slist);
		
		if(!OperateDB.insert(sql)) {
			isSuccess = false;
		} 
		
		return isSuccess;
	}
	
	
}
